package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;

import vo.Comment;
import JDBC.DatabaseDao;
import Utils.POSTool;
import Utils.Printers;

public class CommentDao {
	static Connection con = DatabaseDao.getConnection();

	/**
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static String[] getAllIds() {
		String[] result = new String[10000000];
		try {
			String sql = "select * from comments";

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int i = 0;
			while (rs.next()) {
				result[i] = rs.getString("user_id");
				i++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author: huangshuigui int getWordCount(String commentId); commentId may
	 *          be too big! 获取一条评论的词语数
	 * @param commentId
	 *            : 评论的标识，用字符串来表示
	 * @return int: 评论经过ictclas分词之后的长度
	 */
	public static int getWordCount(String commentId) {
		int result = 0;
		String sql = String.format("select content from comments where comment_id=%s", commentId);
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				String content = rs.getString("content");
				System.out.println("comment content: " + content);
				if (content != null) {
					String pieces = POSTool.SegmentSentence(content);
					if (pieces.length() > 0) {
						String[] terms = pieces.split(" +");
						result = terms.length;
					} else {
						result = 0;
					}
					System.out.println("word count in comment content: " + pieces + " : " + result);
				}
			} else {
				System.out.println("comment null for comment_id = " + commentId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get count of statuses by different kinds of statuses
	 * @return
	 */
	public static int getCountByCondition(Comment comment) {
		int result = 0;
		try {
			String sql = initCountCondition(comment);
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get statuses by different kinds of statuses
	 * @return
	 */
	public static ArrayList<Comment> getByCondition(Comment comment) {
		ArrayList<Comment> result = new ArrayList<Comment>();
		try {
			String sql = initCondition(comment);
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Comment tmp = new Comment();
				tmp.setUserId(rs.getString("user_id"));
				tmp.setContent(rs.getString("content"));
				tmp.setSourceName(rs.getString("source_name"));
				tmp.setCreatedAt(rs.getTimestamp("created_at"));
				result.add(tmp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public static String initCountCondition(Comment comment) {
		String result = "select count(*) from comments";
		if (comment == null)
			return result;

		boolean firstCondition = true;
		result += " where ";
		if (comment.getUserId() != null && !comment.getUserId().equals("")) {
			result += "user_id='" + comment.getUserId() + "'";
			firstCondition = false;
		}
		if (comment.getStartTime() != null && !comment.getStartTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at> '" + comment.getStartTime() + "'";
			firstCondition = false;
		}
		if (comment.getEndTime() != null && !comment.getEndTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at <'" + comment.getEndTime() + "'";
			firstCondition = false;
		}
		if (comment.getSourceName() != null && !comment.getSourceName().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "source_name='" + comment.getSourceName() + "'";
			firstCondition = false;
		}
		return result;

	}

	public static String initCondition(Comment comment) {
		String result = "select * from comments";
		if (comment == null)
			return result;

		boolean firstCondition = true;
		result += " where ";
		if (comment.getUserId() != null && !comment.getUserId().equals("")) {
			result += "user_id='" + comment.getUserId() + "'";
			firstCondition = false;
		}
		if (comment.getStartTime() != null && !comment.getStartTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at> '" + comment.getStartTime() + "'";
			firstCondition = false;
		}
		if (comment.getEndTime() != null && !comment.getEndTime().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "created_at <'" + comment.getEndTime() + "'";
			firstCondition = false;
		}
		if (comment.getSourceName() != null && !comment.getSourceName().equals("")) {
			if (firstCondition == false)
				result += " and ";
			result += "source_name='" + comment.getSourceName() + "'";
			firstCondition = false;
		}
		return result;

	}

	/**
	 * @author 韩 闻文 total comments
	 * @return
	 */
	public static int getLength(String commentId) {
		int result = 0;
		try {
			String sql = "select content from comments where comment_id=" + commentId;
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result = rs.getString(1).length();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 average length of all comments, unit is "characters" not
	 *         "words"
	 * @return
	 */
	public static double getAVGLength() {
		double result = 0;
		double sum = 0.0;
		int count = 0;
		try {
			String sql = "select content from comments";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				sum += rs.getString(1).length();
				System.out.println(rs.getString(1).length());
				count++;
			}
			result = sum / count;
			System.out.println("sum,count:" + sum + "," + count);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 total comments
	 * @return
	 */
	public static ArrayList<String> getSourceTypes() {
		ArrayList<String> result = new ArrayList<String>();
		try {
			String sql = "select source_name from comments group by source_name order by count(*) desc";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result.add(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 total comments
	 * @return
	 */
	public static Hashtable<String, Integer> getCommnetF_FF() {
		Hashtable<String, Integer> F_FF = new Hashtable<String, Integer>();
		try {
			String sql = "select count(*) from comments group by(user_id)";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String count = rs.getString(1);
				if (F_FF.containsKey(count))
					F_FF.put(count, F_FF.get(count) + 1);
				else
					F_FF.put(count, 1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return F_FF;
	}

	/**
	 * @author 韩 闻文 the crawl time of the first and last comments
	 * @return
	 */
	public static String getCrawledTimeRange() {
		String result = "";
		try {
			String sql = "select min(update_time),max(update_time)  from comments ";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rs.next();
			result += rs.getString(1) + "@";
			result += rs.getString(2);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @author 韩 闻文 get length of all comments belonged to the same user
	 * @param userId
	 * @return
	 */
	public static int getLengthByUserId(String userId) {
		int result = 0;
		try {
			String sql = "select content from comments user_id=" + userId;
			System.out.println(sql);
			Statement stmt = DatabaseDao.getConnection().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				result += rs.getString(1).length();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return result;

	}

	/**
	 * 读取所有的statusesCount
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static ArrayList<Integer> getCommentsLengths() {
		ArrayList<Integer> commentsLengths = new ArrayList<Integer>();
		try {
			String sql = "select * from comments";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String comment = rs.getString("content");
				commentsLengths.add(comment.length());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return commentsLengths;
	}

	/**
	 * 读取所有的statusesCount
	 * 
	 * @author 韩 闻文
	 * @return
	 */
	public static ArrayList<Integer> getCommentsCounts() {
		ArrayList<Integer> commentsCounts = new ArrayList<Integer>();
		try {
			String sql = "select user_id,count(*) from comments group by (user_id)";
			System.out.println(sql);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				int commentCount = rs.getInt(2);
				commentsCounts.add(commentCount);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return commentsCounts;
	}

	public static void main(String args[]) {
		// int length = getLength("3425324010793086");
		// System.out.println(length);
		// CommentDao.getWordCount("3425324010793086");
		// ArrayList<String> sourceTypes = getSourceTypes();
		// System.out.println(sourceTypes);
		// int sourceTypeCount = getCountBySourceType("新浪微博");
		// System.out.println(sourceTypeCount);
		// double avgLength=getAVGLength();
		// System.out.println(avgLength);
		// String crawledTimeRange=CommentDao.getCrawledTimeRange();
		// System.out.println(crawledTimeRange);
		Printers.htPrinters(getCommnetF_FF());
	}

}
